-- drop database WEBCH

create database WEBCH
go
use WEBCH
go
create TABLE [NguoiDung] (
	[IDNguoiDung] [int] IDENTITY  NOT NULL ,
	[HoTen] [nvarchar] (32)  ,
	[SDT] [int]  , 
	[NgaySinh] smalldatetime,
	[DiaChi] [nvarchar] (32) ,
	[GioiTinh] [int]    ,
	[UserName] [nvarchar] (32) ,
	[MatKhau] [nvarchar] (32)  ,
	[LoaiND] [int]  , 

	primary key ([IDNguoiDung])
) 
CREATE TABLE [LoaiND] (
	[IDLoaiND] [int] IDENTITY  NOT NULL ,
	[TenLoai] [nvarchar] (20),
	primary key ([IDLoaiND])
)

CREATE TABLE [DanhMuc] (
	[IDDanhMuc] [int] IDENTITY  NOT NULL ,
	[TenDanhMuc] [nvarchar] (20),
	primary key ([IDDanhMuc])
)

DELETE DanhMuc WHERE IDDanhMuc = '1'



CREATE TABLE [SanPham] (
	[IDSanPham] [int] IDENTITY  NOT NULL ,
	[TenSP] [nvarchar] (20),
	[DonGia] [int] ,
	[SoLuong] [int],
	[MoTa] [nvarchar] (256),
	[Url] [nvarchar] (256),
	[IDDanhMuc] [int]
	primary key ([IDSanPham])
)


CREATE TABLE [DonHang] (
	[IDDonHang] [int] IDENTITY  NOT NULL ,
	[IDNguoiDung] [int],
	[ThanhTien] [int],
	[NgayDat] [datetime],
	[TinhTrang] [int]  , 
	
	
	primary key ([IDDonHang])
)

CREATE TABLE [ChiTietDonHang] (
	
	[IDDonHang] [int],
	[IDSanPham] [int],
	[SoLuong] [int],
	[DonGia] [int]
	
	primary key ([IDDonHang],[IDSanPham])
)

alter table [ChiTietDonHang] add constraint fk_IDDonHang foreign key (IDDonHang) references DonHang(IDDonHang)
alter table [ChiTietDonHang] add constraint fk_IDSanPham foreign key (IDSanPham) references SanPham(IDSanPham)
alter table DonHang add constraint fk_IDNguoiDung foreign key (IDNguoiDung) references NguoiDung(IDNguoiDung) ON DELETE CASCADE
alter table NguoiDung add constraint fk_LoaiND foreign key (LoaiND) references LoaiND(IDLoaiND)
alter table SanPham add constraint fk_IDDanhMuc foreign key (IDDanhMuc) references DanhMuc(IDDanhMuc) ON DELETE CASCADE

insert into DanhMuc(TenDanhMuc)values ('abc')

insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Motorola 156 MX-VL',10,10,'dien thoai','images/p1.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Iphone Apple',270,10,'dien thoai','images/p4.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Samsung Webcam',270,10,'dien thoai','images/p5.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('LapTop SamSum',270,10,'dien thoai','images/laptop.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Motorola 156 MX-VL',10,10,'dien thoai','images/p1.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Iphone Apple',270,10,'dien thoai','images/p4.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Samsung Webcam',270,10,'dien thoai','images/p5.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('LapTop SamSum',270,10,'dien thoai','images/laptop.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Motorola 156 MX-VL',10,10,'dien thoai','images/p1.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Iphone Apple',270,10,'dien thoai','images/p4.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('Samsung Webcam',270,10,'dien thoai','images/p5.gif',1)
insert into SanPham(TenSP,DonGia,SoLuong,MoTa,Url,IDDanhMuc) values ('LapTop SamSum',270,10,'dien thoai','images/laptop.gif',1)

insert into LoaiND(TenLoai) values('Admin')
insert into LoaiND(TenLoai) values('KhachHang')

insert into NguoiDung(HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,LoaiND) values ('Nguyen Van A',123456,'','',1,'admin','admin',1)
insert into NguoiDung(HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,LoaiND) values ('Nguyen Van B',123456,'','',1,'NVB','NVB',2)
insert into NguoiDung(HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,LoaiND) values ('Nguyen Van C',123456,'','',1,'NVC','NVC',2)
insert into NguoiDung(HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,LoaiND) values ('Nguyen Van D',123456,'','',1,'NVD','NVD',2)
insert into NguoiDung(HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,LoaiND) values ('Nguyen C',123456,'','',1,'NC','NC',2)
insert into NguoiDung(HoTen,SDT,NgaySinh,DiaChi,GioiTinh,UserName,MatKhau,LoaiND) values ('Nguyen Van F',123456,'','',1,'NVF','NVF',2)


insert into DonHang(IDNguoiDung,ThanhTien,NgayDat,TinhTrang) values (2,50000,null,1)
insert into DonHang(IDNguoiDung,ThanhTien,NgayDat,TinhTrang) values (3,100000,null,1)
insert into DonHang(IDNguoiDung,ThanhTien,NgayDat,TinhTrang) values (4,60000,null,1)
insert into DonHang(IDNguoiDung,ThanhTien,NgayDat,TinhTrang) values (5,40000,null,1)
insert into DonHang(IDNguoiDung,ThanhTien,NgayDat,TinhTrang) values (6,50000,null,1)



DELETE NguoiDung WHERE IDNguoiDung=2